package com.webmall.service;

import com.webmall.dao.BaseDao;
import com.webmall.entity.UserEntity;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

/**
 * 专门用来针对 用户操作 设置的dao
 * 在servlet中调用
 */
public class webmall_UserDao {

    /**
     * 添加用户到数据库
     * @param user
     * @return
     */
    //添加操作
    public static int insertUser(UserEntity user){
        //1。声明sql语句
        //into 表名称，后面是格式化date
        String sql = "INSERT INTO table_user VALUES(?,?,?,?,DATE_FORMAT(?,'%Y-%m-%d'),?,?,?,?,?) ";

        //2。调动DAO-第二个参数为object数数组
        Object[] params = {
                user.getUSER_ID(),
                user.getUSER_NAME(),
                user.getUSER_PWD(),
                user.getUSER_SEX(),
                user.getUSER_BIRTHDAY(),
                user.getUSER_IDENITY_CODE(),
                user.getUSER_EMAIL(),
                user.getUSER_MOBILE(),
                user.getUSER_ADDRESS(),
                user.getUSER_STATUS()};


   //    return BaseDao.sqlOperat(sql,null);
        return BaseDao.sqlOperat(sql,params);
    }
    /**
     * 遍历查询数据库
     */
    public static ArrayList<UserEntity> selectAll(int cpage, int count, String keyword){
        //声明列表及结果集
        ArrayList<UserEntity> list= new ArrayList<UserEntity>();
        ResultSet rs = null;//返回的结果集
        //获取连接对象
        Connection conn = BaseDao.getConn();
        PreparedStatement ps = null;

        try {
            String sql ="";
            if (keyword !=null){//用户搜索查询
                sql = "SELECT * FROM table_user WHERE USER_ID like ? ORDER BY USER_ID desc limit ?,?";
                ps = conn.prepareStatement(sql);

                ps.setString(1, "%"+keyword+"%");
                ps.setInt(2,(cpage-1)*count);
                ps.setInt(3,count);

            }else {
                sql = "SELECT * FROM table_user ORDER BY USER_ID desc limit ?,?";//从哪儿开始取 去多少
                ps = conn.prepareStatement(sql);

                ps.setInt(1,(cpage-1)*count);
                ps.setInt(2,count);
            }


            rs=ps.executeQuery();
            while (rs.next()){

                //getString:获取当前列的数据
                UserEntity user = new UserEntity(
                        rs.getString("USER_ID"),
                        rs.getString("USER_NAME"),
                        rs.getString("USER_PWD"),
                        rs.getString("USER_SEX"),
                        rs.getString("USER_BIRTHDAY"),
                        rs.getString("USER_IDENITY_CODE"),
                        rs.getString("USER_EMAIL"),
                        rs.getString("USER_MOBILE"),
                        rs.getString("USER_ADDRESS"),
                        rs.getInt("USER_STATUS")
                );
                list.add(user);
            }

        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.closeSql(rs,ps,conn);
        }


        return list;


    }

    /**
     * 分页处理时获取总页数和总条数
     * 传入的参数是每一页设置的显示数目
     */
    public static int[] totalPage(int count,String keyword){
        int[] arr={0,1};//第一项为总记录数 第二项为页数

        Connection conn = BaseDao.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;


        try {
            String sql = "";
            if (null != keyword){
                sql = "SELECT count(*) FROM table_user WHERE USER_ID like ? ";
                ps = conn.prepareStatement(sql);
                ps.setString(1, "%"+keyword+"%");

            }else {
                sql = "SELECT count(*) FROM table_user";
                ps=conn.prepareStatement(sql);
            }


            rs = ps.executeQuery();
            while (rs.next()){
               arr[0]= rs.getInt(1);

            }
            if (arr[0]%count ==0){
                arr[1] = arr[0]/count;
            }else {
                arr[1] = arr[0]/count+1;
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return arr;
    }


    /**
     * 根据id判断重复

     * @return
     */
    public static int countById(String id){


        int count =0;
        Connection conn = BaseDao.getConn();
        PreparedStatement ps = null;
        ResultSet rs = null;



        try {
            String sql = "SELECT count(*) FROM table_user WHERE USER_ID like ? ";
            ps = conn.prepareStatement(sql);
            ps.setString(1, id);

            rs = ps.executeQuery();

            while (rs.next()){
                count=rs.getInt(1);
            }


        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }

        return count;
    }
    /**
     * 根据id进行遍历搜索
     */

    public static UserEntity selectById(String id){

        ResultSet rs = null;
        UserEntity user = null;
        Connection conn = BaseDao.getConn();
        PreparedStatement ps = null;


        try {
            String sql = "SELECT m.*, DATE_FORMAT(m.USER_BIRTHDAY,'%Y-%m-%d')birthday FROM table_user m WHERE USER_ID like ?";
            ps=conn.prepareStatement(sql);
            ps.setString(1,id);
            rs = ps.executeQuery();

            if (rs.next()){
                 user = new UserEntity(
                        rs.getString("USER_ID"),
                        rs.getString("USER_NAME"),
                        rs.getString("USER_PWD"),
                        rs.getString("USER_SEX"),
                        rs.getString("birthday"),
                        rs.getString("USER_IDENITY_CODE"),
                        rs.getString("USER_EMAIL"),
                        rs.getString("USER_MOBILE"),
                        rs.getString("USER_ADDRESS"),
                        rs.getInt("USER_STATUS")
                );
            }
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        } finally {
            BaseDao.closeSql(rs,ps,conn);
        }

        return user;

    }
    /**
     * 根据传来的用户进行信息修改
     */

    public static int updateById(UserEntity user){
        //1。声明sql语句
        //into 表名称，后面是格式化date
        String sql = "update table_user set " +
                "USER_NAME=?,USER_PWD=?," +
                "USER_SEX=?,USER_BIRTHDAY=DATE_FORMAT(?,'%Y-%m-%d')," +
                "USER_IDENITY_CODE=?,USER_EMAIL=?,USER_MOBILE=?," +
                "USER_ADDRESS=?,USER_STATUS=? where USER_ID=? ";

        //2。调动DAO-第二个参数为object数数组
        Object[] params = {

                user.getUSER_NAME(),
                user.getUSER_PWD(),
                user.getUSER_SEX(),
                user.getUSER_BIRTHDAY(),
                user.getUSER_IDENITY_CODE(),
                user.getUSER_EMAIL(),
                user.getUSER_MOBILE(),
                user.getUSER_ADDRESS(),
                user.getUSER_STATUS(),
                user.getUSER_ID()};


        //    return BaseDao.sqlOperat(sql,null);
        return BaseDao.sqlOperat(sql,params);
    }

    /**
     * 删除
     * @param id
     * @return
     */
    public static int deleteByID(String id){
        String sql = "delete from table_user where USER_ID=? and USER_STATUS!=2";

        Object[] params = {id};

        return BaseDao.sqlOperat(sql,params);
    }
}
